package model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import domain.TransportInfo;

public class MessageDao {
	ResultSet rs = null;
	Statement st = null;
	PreparedStatement pt = null;
	int numberOfColumns;

	public void close() {
		try {
			if (pt != null) {
				pt.close();
			}
			if (st != null) {
				st.close();
			}
			if (rs != null) {
				rs.close();
			}

		} catch (Exception e) {
		}
	}

	public Boolean insertMessage(Connection cn, int id, String name,
			String buy_way, String tra_way, String comment, float money,
			String pet) {
		String sql = "insert into message_info(User_Id,Message_Name,Message_Date,Buy_Way,Traction_Way,Message_Comment,CurrentMoney,Pet_Comment) values(?,?,(select CURDATE()),?,?,?,?,?)";
		try {
			pt = cn.prepareStatement(sql);
			String url = "select Message_Id from Message_Info";
			st = cn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			rs = st.executeQuery(url);
			pt.setInt(1, id);
			pt.setString(2, name);
			pt.setString(3, buy_way);
			pt.setString(4, tra_way);
			pt.setString(5, comment);
			pt.setFloat(6, money);
			pt.setString(7, pet);
			rs.moveToInsertRow();
			pt.executeUpdate();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			close();
		}
	}

	public ArrayList<TransportInfo> searchMessage(Connection cn, int User_id,
			int i, int pageSize, int pageNow) {

		String sql = "select Message_Id,user_info.User_Name,Message_Name,Message_Date,Message_State,Buy_Way,Traction_Way,Message_Comment,CurrentMoney,Pet_Comment from message_info,user_info where user_info.User_Id=message_info.User_Id and user_info.User_Id="
				+ User_id+" order by Message_Date desc";
		try {
			
				ArrayList<TransportInfo> messages = new ArrayList<TransportInfo>();
				if (pageSize > 0 && pageNow > 0) {
				st = cn.createStatement();
				rs = st.executeQuery(sql);
				rs.absolute(i);
				int j = 0;
				do {
					TransportInfo message = new TransportInfo();
					message.setMessage_id(rs.getInt(1));
					message.setUser_Id(User_id);
					message.setUser_Name(rs.getString(2));
					message.setMessage_Name(rs.getString(3));
					message.setMessage_Date(rs.getDate(4).toString());
					message.setMessage_State(rs.getInt(5));
					message.setBuy_Way(rs.getString(6));
					message.setTraction_Way(rs.getString(7));
					message.setMessage_Comment(rs.getString(8));
					message.setMoney(rs.getFloat(9));
					message.setPet_message(rs.getString(10));
					messages.add(message);
					j++;
				} while (rs.next() && j < pageSize);
			}
			return messages;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			close();
		}
	}
	public int searchRowCount(Connection cn,int User_id) {
		try {
			String url = "select Message_Id,user_info.User_Name,Message_Name,Message_Date,Message_State,Buy_Way,Traction_Way,Message_Comment,CurrentMoney,Pet_Comment from message_info,user_info where user_info.User_Id=message_info.User_Id and user_info.User_Id="
				+ User_id;
			st = cn.createStatement();
			rs = st.executeQuery(url);
			rs.last();
			return rs.getRow();
		} catch (Exception e) {
			return 0;
		} finally {
			close();
		}
	}
	public int searchMessageCount(Connection cn) {
		int count=0;
		try {
			String url = "select count(Message_Id) from message_info where Message_State=1";
			st = cn.createStatement();
			rs = st.executeQuery(url);
			if(rs.next()){
				count=rs.getInt(1);
			}
			return count;
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		} finally {
			close();
		}
	}
	public List<TransportInfo> searchMessages(Connection cn, int start,
			int limit) {
        List<TransportInfo> messages=new ArrayList<TransportInfo>();
		String sql = "select Message_Id,Message_Name,Message_Date,Traction_Way,CurrentMoney,Pet_Comment from message_info where Message_State=1 order by Message_Date desc"+" limit "+start+","+limit+" ";
		try {
			
				st = cn.createStatement();
				rs = st.executeQuery(sql);
				while(rs.next()){
					TransportInfo one=new TransportInfo();
					one.setMessage_id(rs.getInt(1));
					one.setMessage_Name(rs.getString(2));
					one.setMessage_Date(rs.getDate(3).toString());
					one.setTraction_Way(rs.getString(4));
					one.setMoney(rs.getFloat(5));
					one.setPet_message(rs.getString(6));
					messages.add(one);
				}
			return messages;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			close();
		}
	}
	public boolean confirmOrderDB(Connection cn,int id) {
		try {
			String url = "update message_info set Message_State=2 where Message_Id="+id;
			st = cn.createStatement();
			st.executeUpdate(url);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			close();
		}
	}
	public boolean defuseOrderDB(Connection cn,int id) {
		try {
			String url = "update message_info set Message_State=0 where Message_Id="+id;
			st = cn.createStatement();
			st.executeUpdate(url);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			close();
		}
	}
}